﻿using System;
using System.Collections.Generic;
using System.Text;
using System.Data.Common;
using System.Data.SqlClient;
using System.Data.OleDb;
using System.Data;
using System.Configuration;

namespace CodeBuilderBase
{
    public enum ConnType
    {
        Sql2005,
        Sql2000,
        OleDb
    }

    public class ColumAttribute
    {
        public ColumAttribute(string value,string name)
        {
            this.AttributeName = name;
            this.AttributeValue = value;
        }
        private string _AttributeName;
        /// <summary>
        /// 属性名
        /// </summary>
        public string AttributeName
        {
            get { return _AttributeName; }
            set { _AttributeName = value; }
        }
        private string _AttributeValue;
        /// <summary>
        /// 属性值
        /// </summary>
        public string AttributeValue
        {
            get { return _AttributeValue; }
            set { _AttributeValue = value; }
        }
    }

    public class TableModel
    {
        private string _ColumName;
        /// <summary>
        /// 字段名字
        /// </summary>
        public string ColumName
        {
            get { return _ColumName; }
            set { _ColumName = value; }
        }

        private string _ColumType;
        /// <summary>
        /// 字段类型
        /// </summary>
        public string ColumType
        {
            get { return _ColumType; }
            set { _ColumType = value; }
        }
        private string _ColumByteCount;
        /// <summary>
        /// 字段占用空间
        /// </summary>
        public string ColumByteCount
        {
            get { return _ColumByteCount; }
            set { _ColumByteCount = value; }
        }
        private string _IsIdentity;
        /// <summary>
        /// 是否是子增长列
        /// </summary>
        public string IsIdentity
        {
            get { return _IsIdentity; }
            set { _IsIdentity = value; }
        }
        private string _IsPK;
        /// <summary>
        /// 是否是主键
        /// </summary>
        public string IsPK
        {
            get { return _IsPK; }
            set { _IsPK = value; }
        }
        private string _ColoumLength;
        /// <summary>
        /// 字段长度
        /// </summary>
        public string ColoumLength
        {
            get { return _ColoumLength; }
            set { _ColoumLength = value; }
        }
        private string _NumberOfDecimalPlaces;
        /// <summary>
        /// 字段小数位数
        /// </summary>
        public string NumberOfDecimalPlaces
        {
            get { return _NumberOfDecimalPlaces; }
            set { _NumberOfDecimalPlaces = value; }
        }
        private string _IsAllowNull;
        /// <summary>
        /// 字段是否允许为空
        /// </summary>
        public string IsAllowNull
        {
            get { return _IsAllowNull; }
            set { _IsAllowNull = value; }
        }
        private string _DefaultValue;
        /// <summary>
        /// 字段默认值
        /// </summary>
        public string DefaultValue
        {
            get { return _DefaultValue; }
            set { _DefaultValue = value; }
        }
        private string _ColumRemark;
        /// <summary>
        /// 字段说明
        /// </summary>
        public string ColumRemark
        {
            get { return _ColumRemark; }
            set { _ColumRemark = value; }
        }
        private string _TableName;
        /// <summary>
        /// 表名字
        /// </summary>
        public string TableName
        {
            get { return _TableName; }
            set { _TableName = value; }
        }
        private string _TableRemark;
        /// <summary>
        /// 表说明
        /// </summary>
        public string TableRemark
        {
            get { return _TableRemark; }
            set { _TableRemark = value; }
        }
        private string _ColumId;
        /// <summary>
        /// 字段ID
        /// </summary>
        public string ColumId
        {
            get { return _ColumId; }
            set { _ColumId = value; }
        }
    }

    public class Operate
    {
        public List<TableModel> GetTableModelListAll(ConnType cType)
        {
            return GetTableModelList("", cType);
        }

        public TableModel GetTableModel(string tableName, ConnType cType)
        {
            return GetTableModelList(tableName, cType).Find(delegate(TableModel tb) { return tb.TableName == "tableName"; });
        }

        public List<TableModel> GetTableModelList(string tableName, ConnType cType)
        {
            string sql = string.Format(@"SELECT
                                TableName       = d.name,
                                TableRemark     = case when a.colorder=1 then isnull(f.value,'') else '' end,
                                ColumId   = a.colorder,
                                ColumName     = a.name,
                                IsIdentity       = case when COLUMNPROPERTY( a.id,a.name,'IsIdentity')=1 then '√'else '' end,
                                IsPK       = case when exists(SELECT 1 FROM sysobjects where xtype='PK' and parent_obj=a.id and name in (
                                                 SELECT name FROM sysindexes WHERE indid in( SELECT indid FROM sysindexkeys WHERE id = a.id AND colid=a.colid))) then '√' else '' end,
                                ColumType       = b.name,
                                ColumByteCount = a.length,
                                ColoumLength       = COLUMNPROPERTY(a.id,a.name,'PRECISION'),
                                NumberOfDecimalPlaces   = isnull(COLUMNPROPERTY(a.id,a.name,'Scale'),0),
                                IsAllowNull     = case when a.isnullable=1 then '√'else '' end,
                                DefaultValue     = isnull(e.text,''),
                                ColumRemark   = isnull(g.[value],'')
                            FROM
                                syscolumns a
                            left join
                                systypes b
                            on
                                a.xusertype=b.xusertype
                            inner join
                                sysobjects d
                            on
                                a.id=d.id and d.xtype='U' and d.name<>'dtproperties'
                            left join
                                syscomments e
                            on
                                a.cdefault=e.id ");
            switch (cType)
            {
                case ConnType.Sql2000:
                    {
                        string sqlMid = @"left join sysproperties g on a.id=g.id and a.colid=g.smallid
left join sysproperties f on d.id=f.id and f.smallid=0";
                        sql += sqlMid;
                        string sqlEnd = string.Format(@"where
                                1= 1 {0}
                            order by
                                a.id,a.colorder", tableName == "" ? "" : "And d.name='" + tableName + "");
                        sql += sqlEnd;
                    }
                    break;
                case ConnType.Sql2005:
                    {
                        string sqlMid = string.Format(@"left join
                            sys.extended_properties   g
                            on
                                a.id=G.major_id and a.colid=g.minor_id
                            left join
                            sys.extended_properties f
                            on
                                d.id=f.major_id and f.minor_id=0");
                        sql += sqlMid;
                        string sqlEnd = string.Format(@"where
                                1= 1 {0}
                            order by
                                a.id,a.colorder", tableName == "" ? "" : "And d.name='" + tableName + "");
                        sql += sqlEnd;
                    }
                    break;
            }
            DbConnection conn = null;
            DbDataAdapter dad = null;
            DataTable dt =new DataTable("codeBuilderTable");
            try
            {
                conn = GetConn(cType);
                dad = GetDataAdapter(cType, conn, sql);
                if (dad != null)
                {
                    dad.Fill(dt);
                }
            }
            catch(Exception ex)
            {
                throw ex;
            }
            finally
            {
                if (conn != null)
                {
                    conn.Close();
                }
            }
            List<TableModel> list = new List<TableModel>();
            if(dt!=null && dt.Rows.Count > 0)
            {
                foreach(DataRow row in dt.Rows)
                {
                    TableModel tm = new TableModel();
                    tm.ColoumLength = row["ColoumLength"].ToString();
                    tm.ColumByteCount = row["ColumByteCount"].ToString();
                    tm.ColumId = row["ColumId"].ToString();
                    tm.ColumName = row["ColumName"].ToString();
                    tm.ColumRemark = row["ColumRemark"].ToString();
                    tm.ColumType = row["ColumType"].ToString();
                    tm.DefaultValue = row["DefaultValue"].ToString();
                    tm.IsAllowNull = row["IsAllowNull"].ToString();
                    tm.IsIdentity = row["IsIdentity"].ToString();
                    tm.IsPK = row["IsPK"].ToString();
                    tm.NumberOfDecimalPlaces = row["NumberOfDecimalPlaces"].ToString();
                    tm.TableName = row["TableName"].ToString();
                    tm.TableRemark = row["TableRemark"].ToString();
                    list.Add(tm);
                }
            }
            return list;
        }

        DbDataAdapter GetDataAdapter(ConnType cType, DbConnection conn, string cmdText)
        {
            DbDataAdapter dad = null;
            if (conn != null)
            {
                switch (cType)
                {
                    case ConnType.OleDb:
                        {
                            dad = new OleDbDataAdapter(cmdText, (OleDbConnection)conn);
                        }
                        break;
                    case ConnType.Sql2000:
                        {
                            dad = new SqlDataAdapter(cmdText, (SqlConnection)conn);
                        }
                        break;
                    case ConnType.Sql2005:
                        {
                            dad = new SqlDataAdapter(cmdText, (SqlConnection)conn);
                        }
                        break;
                }
            }
            return dad;
        }
        /// <summary>
        /// 测试连接
        /// </summary>
        /// <param name="cType"></param>
        /// <param name="connString"></param>
        /// <param name="err"></param>
        /// <returns></returns>
        public bool TestConn(ConnType cType,string connString,out string err)
        {
            bool flag = false;
            err = "";
            DbConnection conn = null;
            try
            {
                conn = GetConn(cType, connString);
                if (conn != null)
                {
                    flag = true;
                }
            }
            catch(Exception ex)
            {
                err = ex.Message;
            }
            finally
            {
                if (conn != null)
                {
                    conn.Close();
                }
            }
            return flag;
        }

        public DbConnection GetConn(ConnType cType)
        {
            DbConnection conn = null;
            try
            {
                string connStr = GetApp("Conn");
                conn = GetConn(cType, connStr);
            }
            catch (Exception ex)
            {
                if (conn != null)
                {
                    conn.Close();
                }
                throw ex;
            }
            return conn;
        }

        public DbConnection GetConn(ConnType cType,string connString)
        {
            DbConnection conn = null;
            string connStr = connString;
            if (connStr == null)
            {
                return null;
            }
            switch (cType)
            {
                case ConnType.Sql2000:
                    {
                        conn = new SqlConnection(connStr);
                    }
                    break;
                case ConnType.Sql2005:
                    {
                        conn = new SqlConnection(connStr);
                    }
                    break;
                case ConnType.OleDb:
                    {
                        conn = new OleDbConnection(connStr);
                    }
                    break;
            }
            if (conn != null)
            {
                conn.Open();
            }
            return conn;
        }

        /// <summary>
        /// 获取app文件
        /// </summary>
        /// <returns></returns>
        public static string GetApp(string appKey)
        {
            string temp = "";
            try
            {
                temp = ConfigurationManager.AppSettings[appKey];
            }
            catch
            {
                SetApp(appKey, "");
                temp = "";
            }
            return temp;
        }
        /// <summary>
        /// 设置app文件
        /// </summary>
        /// <param name="appKey"></param>
        /// <param name="appValue"></param>
        /// <returns></returns>
        public static bool SetApp(string appKey, string appValue)
        {
            bool flag = false;
            try
            {
                Configuration config = ConfigurationManager.OpenExeConfiguration(System.Configuration.ConfigurationUserLevel.None);
                bool hasKey = false;
                foreach (string k in config.AppSettings.Settings.AllKeys)
                {
                    if (k == appKey)
                    {
                        config.AppSettings.Settings[appKey].Value = appValue;
                        hasKey = true;
                        break;
                    }
                }
                if (!hasKey)
                    config.AppSettings.Settings.Add(appKey, appValue);
                config.Save(ConfigurationSaveMode.Modified);
                ConfigurationManager.RefreshSection("appSettings");
                flag = true;
            }
            catch
            {
                flag = false;
            }
            return flag;
        }
    }
}
